package com.byxy.dao;

import com.byxy.model.BasicInfo;
import com.byxy.util.DBUtil;
import com.byxy.util.DateUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BasicInfoDaoImpl implements BaseDao<BasicInfo> {
	private PreparedStatement psta = null;
	private Connection conn = null;
	private ResultSet rs = null;

	@Override
	public int add(BasicInfo basic) {
		int i = -1;
		int id = basic.getId();
		String name = basic.getName();
		String sex = basic.getSex();
		String familyName = basic.getFamilyName();
		String placeOfBirth = basic.getPlaceOfBirth();
		Date birthday = basic.getBirthday();
		String education = basic.getEducation();
		String post = basic.getPost();
		Date workHours = basic.getWorkHours();
		Date partyHours = basic.getPartyHours();
		Date positiveHours = basic.getPositiveHours();
		boolean status = basic.isStatus();
		Date transitHours = basic.getTransitHours();
		String transferredToTheFormerPartyBranch = basic.getTransferredToTheFormerPartyBranch();
		String domicileLocation = basic.getDomicileLocation();
		String idNumber = basic.getIdNumber();
		String contactNumber = basic.getContactNumber();
		int qqNumber = basic.getQqNumber();
		String wechatNumber = basic.getWechatNumber();
		Date rollOutHours = basic.getRollOutHours();
		String nameOfPartyBranch = basic.getNameOfPartyBranch();
		String sponsor = basic.getSponsor();
		String text = basic.getText();
		int departmentId = basic.getDepartmentId();
		String sql = "INSERT INTO BasicInfo VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setInt(1, id);
			psta.setString(2, name);
			psta.setString(3, sex);
			psta.setString(4, familyName);
			psta.setString(5, placeOfBirth);
			psta.setTimestamp(6, DateUtil.dateToTimestamp(birthday));
			psta.setString(7, education);
			psta.setString(8, post);
			psta.setTimestamp(9, DateUtil.dateToTimestamp(workHours));
			psta.setTimestamp(10, DateUtil.dateToTimestamp(partyHours));
			psta.setTimestamp(11, DateUtil.dateToTimestamp(positiveHours));
			int statusResult = 0;
			if (status) {
				statusResult = 1;
			}
			psta.setInt(12, statusResult);
			psta.setTimestamp(13, DateUtil.dateToTimestamp(transitHours));
			psta.setString(14, transferredToTheFormerPartyBranch);
			psta.setString(15, domicileLocation);
			psta.setString(16, idNumber);
			psta.setString(17, contactNumber);
			psta.setInt(18, qqNumber);
			psta.setString(19, wechatNumber);
			psta.setTimestamp(20, DateUtil.dateToTimestamp(rollOutHours));
			psta.setString(21, nameOfPartyBranch);
			psta.setString(22, sponsor);
			psta.setString(23, text);
			psta.setInt(24, departmentId);
			i = psta.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	@Override
	public int deleteById(int id) {
		int i = -1;
		String sql = "DELETE FROM BasicInfo WHERE id = ?";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setInt(1, id);
			i = psta.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	@Override
	public int update(BasicInfo basic) {
		int i = -1;
		int id = basic.getId();
		String name = basic.getName();
		String sex = basic.getSex();
		String familyName = basic.getFamilyName();
		String placeOfBirth = basic.getPlaceOfBirth();
		Date birthday = basic.getBirthday();
		String education = basic.getEducation();
		String post = basic.getPost();
		Date workHours = basic.getWorkHours();
		Date partyHours = basic.getPartyHours();
		Date positiveHours = basic.getPositiveHours();
		boolean status = basic.isStatus();
		Date transitHours = basic.getTransitHours();
		String transferredToTheFormerPartyBranch = basic.getTransferredToTheFormerPartyBranch();
		String domicileLocation = basic.getDomicileLocation();
		String idNumber = basic.getIdNumber();
		String contactNumber = basic.getContactNumber();
		int qqNumber = basic.getQqNumber();
		String wechatNumber = basic.getWechatNumber();
		Date rollOutHours = basic.getRollOutHours();
		String nameOfPartyBranch = basic.getNameOfPartyBranch();
		String sponsor = basic.getSponsor();
		String text = basic.getText();
		int departmentId = basic.getDepartmentId();
		String sql = "UPDATE BasicInfo SET Name = ?, sex = ?, familyName = ?, placeOfBirth = ?, birthday = ?, education = ?, post = ?, workHours = ?, partyHours = ?, positiveHours = ?, status = ?, transitHours = ?, transferredToTheFormerPartyBranch = ?, domicileLocation = ?, idNumber = ?, contactNumber = ?, qqNumber = ?, wechatNumber = ?, rollOutHours = ?, nameOfPartyBranch = ?, sponsor = ?, Remarks = ? ,departmentId = ? WHERE id = ?";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setString(1, name);
			psta.setString(2, sex);
			psta.setString(3, familyName);
			psta.setString(4, placeOfBirth);
			psta.setTimestamp(5, DateUtil.dateToTimestamp(birthday));
			psta.setString(6, education);
			psta.setString(7, post);
			psta.setTimestamp(8, DateUtil.dateToTimestamp(workHours));
			psta.setTimestamp(9, DateUtil.dateToTimestamp(partyHours));
			psta.setTimestamp(10, DateUtil.dateToTimestamp(positiveHours));
			int statusResult = 0;
			if (status) {
				statusResult = 1;
			}
			psta.setInt(11, statusResult);
			psta.setTimestamp(12, DateUtil.dateToTimestamp(transitHours));
			psta.setString(13, transferredToTheFormerPartyBranch);
			psta.setString(14, domicileLocation);
			psta.setString(15, idNumber);
			psta.setString(16, contactNumber);
			psta.setInt(17, qqNumber);
			psta.setString(18, wechatNumber);
			psta.setTimestamp(19, DateUtil.dateToTimestamp(rollOutHours));
			psta.setString(20, nameOfPartyBranch);
			psta.setString(21, sponsor);
			psta.setString(22, text);
			psta.setInt(23, departmentId);
			psta.setInt(24, id);
			i = psta.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	@Override
	public List<BasicInfo> findAll() {
		List<BasicInfo> list = new ArrayList<BasicInfo>();
		String sql = "SELECT * FROM BasicInfo";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			rs = psta.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String sex = rs.getString("sex");
				String familyName = rs.getString("familyName");
				String placeOfBirth = rs.getString("placeOfBirth");
				Timestamp birthday = rs.getTimestamp("birthday");
				String education = rs.getString("education");
				String post = rs.getString("post");
				Timestamp workHours = rs.getTimestamp("workHours");
				Timestamp partyHours = rs.getTimestamp("partyHours");
				Timestamp positiveHours = rs.getTimestamp("positiveHours");
				int statusResult = rs.getInt("status");
				boolean status = false;
				if (statusResult == 1) {
					status = true;
				}
				Timestamp transitHours = rs.getTimestamp("transitHours");
				String transferredToTheFormerPartyBranch = rs.getString("transferredToTheFormerPartyBranch");
				String domicileLocation = rs.getString("domicileLocation");
				String idNumber = rs.getString("idNumber");
				String contactNumber = rs.getString("contactNumber");
				int qqNumber = rs.getInt("qqNumber");
				String wechatNumber = rs.getString("wechatNumber");
				Timestamp rollOutHours = rs.getTimestamp("rollOutHours");
				String nameOfPartyBranch = rs.getString("nameOfPartyBranch");
				String sponsor = rs.getString("sponsor");
				String text = rs.getString("Remarks");
				int departmentId = rs.getInt("departmentId");
				list.add(new BasicInfo(id, name, sex, familyName, placeOfBirth, DateUtil.timestempToDate(birthday),
						education, post, DateUtil.timestempToDate(workHours), DateUtil.timestempToDate(partyHours),
						DateUtil.timestempToDate(positiveHours), status, DateUtil.timestempToDate(transitHours),
						transferredToTheFormerPartyBranch, domicileLocation, idNumber, contactNumber, qqNumber,
						wechatNumber, DateUtil.timestempToDate(rollOutHours), nameOfPartyBranch, sponsor, text, departmentId));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(rs, psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public BasicInfo findById(int id) {
		BasicInfo basic = null;
		String sql = "SELECT * FROM BasicInfo WHERE id = ?";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setInt(1, id);
			rs = psta.executeQuery();
			while (rs.next()) {
				int uid = rs.getInt("id");
				String name = rs.getString("name");
				String sex = rs.getString("sex");
				String familyName = rs.getString("familyName");
				String placeOfBirth = rs.getString("placeOfBirth");
				Timestamp birthday = rs.getTimestamp("birthday");
				String education = rs.getString("education");
				String post = rs.getString("post");
				Timestamp workHours = rs.getTimestamp("workHours");
				Timestamp partyHours = rs.getTimestamp("partyHours");
				Timestamp positiveHours = rs.getTimestamp("positiveHours");
				int statusResult = rs.getInt("status");
				boolean status = false;
				if (statusResult == 1) {
					status = true;
				}
				Timestamp transitHours = rs.getTimestamp("transitHours");
				String transferredToTheFormerPartyBranch = rs.getString("transferredToTheFormerPartyBranch");
				String domicileLocation = rs.getString("domicileLocation");
				String idNumber = rs.getString("idNumber");
				String contactNumber = rs.getString("contactNumber");
				int qqNumber = rs.getInt("qqNumber");
				String wechatNumber = rs.getString("wechatNumber");
				Timestamp rollOutHours = rs.getTimestamp("rollOutHours");
				String nameOfPartyBranch = rs.getString("nameOfPartyBranch");
				String sponsor = rs.getString("sponsor");
				String text = rs.getString("Remarks");
				int departmentId = rs.getInt("departmentId");
				basic = new BasicInfo(uid, name, sex, familyName, placeOfBirth, DateUtil.timestempToDate(birthday),
						education, post, DateUtil.timestempToDate(workHours), DateUtil.timestempToDate(partyHours),
						DateUtil.timestempToDate(positiveHours), status, DateUtil.timestempToDate(transitHours),
						transferredToTheFormerPartyBranch, domicileLocation, idNumber, contactNumber, qqNumber,
						wechatNumber, DateUtil.timestempToDate(rollOutHours), nameOfPartyBranch, sponsor, text, departmentId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(rs, psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return basic;
	}
	
}
